#include "databaseControl.hpp"
#include "IoTServerConfig.hpp"

DatabaseControl::DatabaseControl()
{
    sql::Driver* driver=sql::mariadb::get_driver_instance();
    conn.reset(driver->connect(fmt::format("jdbc:mariadb://{}/tootIoT",DataBasePath),DBuser,DBpasswd));
    if(!conn)
    {
        OATPP_LOGE("DataBase","Can't connect to database");
    }
}
DatabaseControl::~DatabaseControl()
{
    conn->close();
}

bool DatabaseControl::checkDBconn()
{
    if(!conn)
    {
        OATPP_LOGE("DataBase","Database don't connected");
        return false;
    }
    return true;
}

void DatabaseControl::uploadDeviceValue(int32_t deviceID,std::string value)
{
    if(!checkDBconn()) return;
    try
    {
        //这里实现没有顾及效率，后面得改进一下
        std::shared_ptr<sql::PreparedStatement> stmnt(conn->prepareStatement("INSERT INTO DataCollect (DeviceID,Value) VALUES ( ? , ? );"));
        stmnt->setInt(1,deviceID);
        stmnt->setString(2,value);
        stmnt->executeUpdate();
    }
    catch(const std::exception& e)
    {
        OATPP_LOGE("DataBase","Can't Upload DeviceValue : %s",e.what());
        ReconnectDB();
    }
}

std::list<std::pair<int32_t,DeviceIDItem>> DatabaseControl::downloadDeviceInfo()
{
    std::list<std::pair<int32_t,DeviceIDItem>> newDeviceList;
    if(!checkDBconn()) return newDeviceList;
    try
    {
        std::shared_ptr<sql::Statement> stmnt(conn->createStatement());
        std::unique_ptr<sql::ResultSet> res(stmnt->executeQuery("SELECT DeviceID,DeviceName,DeviceType FROM AllDeviceInfo;"));
        while(res->next())
        {
            newDeviceList.push_back({
                res->getInt("DeviceID"),
                {
                    res->getString("DeviceName").c_str(),
                    res->getInt("DeviceType")
                }
            });
        }
    }
    catch(const sql::SQLException &e)
    {
        OATPP_LOGE("DataBase","Download DeviceInfo catch error : %s",e.what());
        ReconnectDB();
    }
    return newDeviceList;
}

std::list<std::pair<int32_t,DeviceTypeItem>> DatabaseControl::downloadDeviceType()
{
    std::list<std::pair<int32_t,DeviceTypeItem>> newDeviceType;
    if(!checkDBconn()) return newDeviceType;
    try
    {
        std::shared_ptr<sql::Statement> stmnt(conn->createStatement());
        std::unique_ptr<sql::ResultSet> res(stmnt->executeQuery("SELECT TypeID,TypeName,TypeField FROM AllDeviceType;"));
        while(res->next())
        {
            nlohmann::json JsonField=nlohmann::json::parse(res->getString("TypeField").c_str());
            std::vector<std::string> newTypeField;
            newTypeField.resize(JsonField.size());
            for(int i=0;i<JsonField.size();i++)
                newTypeField.at(i)=JsonField[i];
            newDeviceType.push_back({
                res->getInt("TypeID"),
                {
                    res->getString("TypeName").c_str(),
                    newTypeField
                }
            });
        }
    }
    catch(const sql::SQLException &e)
    {
        OATPP_LOGE("DataBase","Download DeviceInfo catch error : %s",e.what());
        ReconnectDB();
    }
    return newDeviceType;
}

std::array<std::string,60> DatabaseControl::downloadDeviceLast1Hour(int32_t deviceID)
{
    std::array<std::string,60> downloadRes={};
    if(!checkDBconn()) return downloadRes;
    try
    {
        std::shared_ptr<sql::Statement> stmnt(conn->createStatement());
        stmnt->execute(fmt::format(R"(CREATE TEMPORARY TABLE result SELECT uploadTime,Value FROM DataCollect WHERE uploadTime > DATE_SUB(NOW(), INTERVAL 1 HOUR) AND DeviceID={};)",deviceID));
        std::unique_ptr<sql::ResultSet> res(stmnt->executeQuery(R"(select uploadTime from result limit 1;)"));
        std::string topOfTime;
        if(res->next())
            topOfTime=res->getString("uploadTime");
        else
            return downloadRes;
        res.reset(stmnt->executeQuery(fmt::format(R"(select TIMEDIFF(uploadTime,'{}') as "tt" , Value from result;)",topOfTime)));
        stmnt->execute(R"(DROP TABLE result;)");
        while (res->next())
        {
            std::string ttime=res->getString("tt").c_str();
            int minIndex=std::stoi(ttime.substr(3,2));
            downloadRes.at(minIndex)=res->getString("Value").c_str();
        }
    }
    catch(const sql::SQLException &e)
    {
        OATPP_LOGE("DataBase","Download Last1Hour catch error : %s",e.what());
        ReconnectDB();
    }
    return downloadRes;
}

std::array<DynamicStruct,24> DatabaseControl::downloadDeviceLast12Hour(int32_t deviceID,std::vector<std::string> typeField)
{
    std::array<DynamicStruct,24> downloadRes={};
    if(!checkDBconn()) return downloadRes;
    try
    {
        std::shared_ptr<sql::Statement> stmnt(conn->createStatement());
        stmnt->execute(fmt::format(R"(CREATE TEMPORARY TABLE result1 SELECT uploadTime,Value FROM DataCollect WHERE uploadTime > DATE_SUB(NOW(), INTERVAL 12 HOUR) AND DeviceID={};)",deviceID));
        std::unique_ptr<sql::ResultSet> res(stmnt->executeQuery(R"(select uploadTime from result1 limit 1;)"));
        std::string topOfTime;
        if(res->next())
            topOfTime=res->getString("uploadTime");
        else
            return downloadRes;
        std::string subSQLStr;
        for(std::string& i : typeField)
            subSQLStr+=fmt::format(R"(, JSON_VALUE(Value,'$.{}') as '{}' )",i,i);
        res.reset(stmnt->executeQuery(fmt::format(R"(select TIMEDIFF(uploadTime,'{}') as 'tt' {} from result1;)",topOfTime,subSQLStr)));
        stmnt->execute(R"(DROP TABLE result1;)");

        //累加值-30分钟内记录数
        std::vector<std::pair<std::vector<double>,int>> allDataCollect(24,{std::vector<double>(typeField.size(),0),0});
        while (res->next())
        {
            long timeLength=Time(res->getString("tt").c_str());
            int pos=timeLength/1800; //每30分钟1800秒
            if(pos>24) break;
            for(int i=0;i<typeField.size();++i)
            {
                allDataCollect[pos].first[i]+=res->getDouble(typeField[i].c_str());
                allDataCollect[pos].second++;
            }
        }
        for(int i=0;i<24;++i)
        {
            for(int j=0;j<typeField.size();++j)
            {
                double avg=0;
                if(allDataCollect[i].second>0)  //防止除数为0
                    avg=allDataCollect[i].first[j]/allDataCollect[i].second;
                downloadRes[i].addKey(typeField[j],avg);
            }
        }
    }
    catch(const sql::SQLException &e)
    {
        OATPP_LOGE("DataBase","Download Last12Hour catch error : %s",e.what());
        ReconnectDB();
    }
    return downloadRes;
}

std::array<DynamicStruct,24> DatabaseControl::downloadDeviceLast24Hour(int32_t deviceID,std::vector<std::string> typeField)
{
    std::array<DynamicStruct,24> downloadRes={};
    if(!checkDBconn()) return downloadRes;
    try
    {
        std::shared_ptr<sql::Statement> stmnt(conn->createStatement());
        stmnt->execute(fmt::format(R"(CREATE TEMPORARY TABLE result2 SELECT uploadTime,Value FROM DataCollect WHERE uploadTime > DATE_SUB(NOW(), INTERVAL 24 HOUR) AND DeviceID={};)",deviceID));
        std::unique_ptr<sql::ResultSet> res(stmnt->executeQuery(R"(select uploadTime from result2 limit 1;)"));
        std::string topOfTime;
        if(res->next())
            topOfTime=res->getString("uploadTime");
        else
            return downloadRes;
        std::string subSQLStr;
        for(std::string& i : typeField)
            subSQLStr+=fmt::format(R"(, JSON_VALUE(Value,'$.{}') as '{}' )",i,i);
        res.reset(stmnt->executeQuery(fmt::format(R"(select TIMEDIFF(uploadTime,'{}') as 'tt' {} from result2;)",topOfTime,subSQLStr)));
        stmnt->execute(R"(DROP TABLE result2;)");

        //累加值-60分钟内记录数
        std::vector<std::pair<std::vector<double>,int>> allDataCollect(24,{std::vector<double>(typeField.size(),0),0});
        while (res->next())
        {
            long timeLength=Time(res->getString("tt").c_str());
            int pos=timeLength/3600; //每60分钟3600秒
            if(pos>24) break;
            for(int i=0;i<typeField.size();++i)
            {
                allDataCollect[pos].first[i]+=res->getDouble(typeField[i].c_str());
                allDataCollect[pos].second++;
            }
        }
        for(int i=0;i<24;++i)
        {
            for(int j=0;j<typeField.size();++j)
            {
                double avg=0;
                if(allDataCollect[i].second>0)  //防止除数为0
                    avg=allDataCollect[i].first[j]/allDataCollect[i].second;
                downloadRes[i].addKey(typeField[j],avg);
            }
        }
    }
    catch(const sql::SQLException &e)
    {
        OATPP_LOGE("DataBase","Download Last24Hour catch error : %s",e.what());
        ReconnectDB();
    }
    return downloadRes;
}

void DatabaseControl::ReconnectDB()
{
    conn->close();
    sql::Driver* driver=sql::mariadb::get_driver_instance();
    conn.reset(driver->connect(fmt::format("jdbc:mariadb://{}/tootIoT",DataBasePath),DBuser,DBpasswd));
    if(!conn)
    {
        OATPP_LOGE("DataBase","Reconnect databases failed");
    }

}

std::array<DynamicStruct,7> DatabaseControl::downloadDeviceLast7Day(int32_t deivceID,std::vector<std::string> typeField)
{
    std::array<DynamicStruct,7> downloadRes={};
    if(!checkDBconn()) return downloadRes;
    try
    {
        std::shared_ptr<sql::Statement> stmnt(conn->createStatement());
        stmnt->execute(fmt::format(R"(CREATE TEMPORARY TABLE result3 SELECT uploadTime,Value FROM DataDayCollect WHERE uploadTime > DATE_SUB(NOW(), INTERVAL 7 DAY) AND DeviceID={};)",deivceID));
        std::unique_ptr<sql::ResultSet> res(stmnt->executeQuery(R"(select uploadTime from result3 limit 1;)"));
        std::string topOfTime;
        if(res->next())
            topOfTime=res->getString("uploadTime");
        else
            return downloadRes;
        std::string subSQLStr;
        for(std::string& i : typeField)
            subSQLStr+=fmt::format(R"(, JSON_VALUE(Value,'$.{}') as '{}' )",i,i);
        res.reset(stmnt->executeQuery(fmt::format(R"(select DATEDIFF(uploadTime,'{}') as 'tt' {} from result3;)",topOfTime,subSQLStr)));
        stmnt->execute(R"(DROP TABLE result3;)");

        while (res->next())
        {
            DynamicStruct newStract;
            for(const std::string& i : typeField)
            {
                newStract.addKey(i,res->getDouble(i));
            }
            downloadRes.at(res->getInt("tt"))=newStract;
        }
        
    }
    catch(const sql::SQLException & e)
    {
        OATPP_LOGE("DataBase","Download Last7Day catch error : %s",e.what());
        ReconnectDB();
    }
    return downloadRes;
}